package controller.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import model.Student;

public class DAOStudentSQL implements DAOStudent {

	@Override
	public List<Student> getAll() {
		List<Student> all = new ArrayList<Student>();
		Long id;
		String username;
		String surname;
		String password;
		Long group;
		Date dateEnter;

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet resultSet = null;

		try {
			InitialContext ic = new InitialContext();
			DataSource ds = (DataSource) ic
					.lookup("java:comp/env/jdbc/system_student");
			con = ds.getConnection();
			stmt = con
					.prepareStatement("SELECT `users`.`id`, `users`.`username`,"
							+ "`users`.`surname`, `users`.`password`, `students`.`group`,"
							+ "`students`.`date_enter` FROM `students`, `users`"
							+ "where(`users`.`id`=`students`.`id_user`)");
			resultSet = stmt.executeQuery();
			while (resultSet.next()) {
				id = resultSet.getLong("id");
				username = resultSet.getString("username");
				surname = resultSet.getString("surname");
				password = resultSet.getString("password");
				group = resultSet.getLong("group");
				dateEnter = resultSet.getDate("date_enter");
				all.add(new Student(id, username, surname, password, group,
						dateEnter));
			}
		} catch (SQLException e) {
		} catch (NamingException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return all;
	}

	@Override
	public List<Student> getStudentByTask(Long idTask) {
		List<Student> all = new ArrayList<Student>();
		Long id;
		String username;
		String surname;
		String password;
		Long group;
		Date dateEnter;
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet resultSet = null;

		try {
			InitialContext ic = new InitialContext();
			DataSource ds = (DataSource) ic
					.lookup("java:comp/env/jdbc/system_student");
			con = ds.getConnection();
			stmt = con
					.prepareStatement("SELECT `users`.`id`, `users`.`username`,"
							+ "`users`.`surname`, `users`.`password`,"
							+ "`students`.`group`,`students`.`date_enter`,"
							+ " `completed_tasks`.`mark` FROM `students`,"
							+ " `users`, `completed_tasks`, `tasks`"
							+ "where(`tasks`.`id`=? and "
							+ "`completed_tasks`.`id_task` = `tasks`.`id`"
							+ "and `completed_tasks`.`id_user`=`students`.`id_user`"
							+ "and `users`.`id`=`students`.`id_user`)");
			stmt.setLong(1, idTask);
			resultSet = stmt.executeQuery();
			while (resultSet.next()) {
				id = resultSet.getLong("id");
				username = resultSet.getString("username");
				surname = resultSet.getString("surname");
				password = resultSet.getString("password");
				group = resultSet.getLong("group");
				dateEnter = resultSet.getDate("date_enter");
				all.add(new Student(id, username, surname, password, group,
						dateEnter));
			}
		} catch (SQLException e) {
		} catch (NamingException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return all;
	}
}
